use master
go

create database Student05
go

use Student05
go

create table StuIS
(
	StuNO nvarchar(10) unique not null,
	StuName nvarchar(10) not null,
	StuAge int not null,
	StuAddress nvarchar(200) ,
	StuSeat nvarchar(8) not null,
	StuSex nvarchar(1) default('男') check(StuSex='男' or StuSex='女')
)

insert into StuIS(StuNO,StuName,StuAge,StuAddress,StuSeat,StuSex)
values('s2501','张秋利','20','美国硅谷','1','女')
insert into StuIS(StuNO,StuName,StuAge,StuAddress,StuSeat,StuSex)
values('s2502','李斯文','18','湖北武汉','2','男')
insert into StuIS(StuNO,StuName,StuAge,StuAddress,StuSeat,StuSex)
values('s2503','马文才','22','湖南长沙','3','男')
insert into StuIS(StuNO,StuName,StuAge,StuAddress,StuSeat,StuSex)
values('s2504','欧阳俊雄','21','湖北武汉','3','女')
insert into StuIS(StuNO,StuName,StuAge,StuAddress,StuSeat,StuSex)
values('s2505','梅超风','20','湖北武汉','4','女')
insert into StuIS(StuNO,StuName,StuAge,StuAddress,StuSeat,StuSex)
values('s2506','陈旋风','19','美国硅谷','5','女')
insert into StuIS(StuNO,StuName,StuAge,StuAddress,StuSeat,StuSex)
values('s2507','陈风','20','美国硅谷','7','女')

create table Grate
(
	ExamNo int primary key identity(1,1),
	StuNo nvarchar(10) references StuIS(StuNO),
	WExam int not null,
	LabExam int not null
)
insert into Grate(StuNO,WExam,LabExam)
values('s2501','50','70')
insert into Grate(StuNO,WExam,LabExam)
values('s2502','60','65')
insert into Grate(StuNO,WExam,LabExam)
values('s2503','86','85')
insert into Grate(StuNO,WExam,LabExam)
values('s2504','40','80')
insert into Grate(StuNO,WExam,LabExam)
values('s2505','70','90')
insert into Grate(StuNO,WExam,LabExam)
values('s2506','85','90')


--数据如图片1,使用上次作业的数据
select * from Grate
select * from StuIS
--1.查询学生的姓名，年龄，笔试成绩和机试成绩
select StuName,StuAge ,WExam,LabExam from  StuIS 
inner join Grate on StuIS.StuNO = Grate.StuNO

--2.查询笔试和机试成绩都在60分以上的学生的学号，姓名，笔试成绩和机试成绩
select StuIS.StuNO ,StuName,WExam,LabExam from  StuIS 
inner join Grate on StuIS.StuNO = Grate.StuNO where Grate.WExam>=60 and Grate.LabExam>=60

--3.查询所有学生的学号，姓名，笔试成绩，机试成绩，没有参加考试的学生的成绩以NULL值填充
select StuIS.StuNO ,StuName,WExam,LabExam from  StuIS 
left join Grate on  StuIS.StuNO = Grate.StuNO 

--4.查询年龄在20以上（包括20）的学生的姓名，年龄，笔试成绩和机试成绩，并按笔试成绩降序排列
select StuName,StuAge,WExam,LabExam from  StuIS 
inner join Grate on StuIS.StuNo = Grate.StuNO where StuIS.StuAge>=20 order by Grate.WExam DESC
--5.查询男女生的机试平均分
select StuSex , avg(LabExam) from StuIS
inner join Grate on StuIS.StuNO = Grate.StuNO group by StuSex
--6.查询男女生的笔试总分
select StuSex , sum(WExam) from StuIS
inner join Grate on StuIS.StuNO = Grate.StuNO group by StuSex